{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 如果本地没有安装工具包，请去掉“#”，再执行\n",
    "# !pip install -i https://mirrors.aliyun.com/pypi/simple/ xlwt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue>读取Excel的内容</font>**<br/>\n",
    "<img src=\"images/Excel工资单明细.png\" align=\"left\"></img>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue>新建Excel的内容（简版）</font>**<br/>\n",
    "**<font color=red>为每个员工创建一个工作表，工作表名是员工姓名</font>**<br/>\n",
    "<img src=\"images/Excel工资单_01.png\" align=\"left\"></img>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 读取Excel，获取字段名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入Excel读写工具包\n",
    "import xlrd\n",
    "import xlwt\n",
    "\n",
    "# 从（from）本地 xlwt_styles.py（.py扩展名省略）文件中，导入（import）创建样式的函数（create_style）\n",
    "from xlwt_styles import create_style\n",
    "\n",
    "# 定义两个变量（xls_filename_rd、xls_filename_wt）\n",
    "xls_filename_rd = \"./files/Excel_工资单明细.xls\" # 读取的 Excel 文件名\n",
    "xls_filename_wt = \"./files/Excel_员工工资单_01.xls\" # 新建的 Excel 文件名\n",
    "# *******************************\n",
    "# 虽然变量命名只要符合命名规则即可，但建议变量命名最好要有意义，对读懂代码很有帮助\n",
    "# xls:Excel扩展名  filename:文件名  rd:read读取  wt:write写入\n",
    "# *******************************\n",
    "\n",
    "# 读取 Excel（xls_filename_rd 变量刚才已经定义），得到工作簿，赋值给变量 workbook_rd\n",
    "workbook_rd = xlrd.open_workbook(xls_filename_rd)\n",
    "\n",
    "# 获取第一个工作表，赋值给变量 sheet_rd\n",
    "sheet_rd = workbook_rd.sheet_by_index(0)\n",
    "\n",
    "# 获取工作表中表格的所有字段（第一行是字段名），记住后面会用到\n",
    "field_names = sheet_rd.row_values(0)\n",
    "print(\"字段名：\",field_names)  # 打印一下看看字段名"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 复习一下什么是列表？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 这是一个列表\n",
    "my_list = [1, 2, 3, 4]\n",
    "print(\"打印列表：\", my_list)\n",
    "print(\"打印第一元素:\", my_list[0])\n",
    "print(\"列表有几个？\",len(my_list))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 列表还可以增加元素（使用列表的 append 方法）\n",
    "my_list.append(66)\n",
    "print(\"*** 增加元素后 ***\")\n",
    "print(\"打印列表：\", my_list)\n",
    "print(\"列表有几个？\",len(my_list))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 列表还可以删除元素（使用列表的 remove 方法）\n",
    "my_list.remove(66)\n",
    "print(\"*** 删除元素后 ***\")\n",
    "print(\"打印列表：\", my_list)\n",
    "print(\"列表有几个？\",len(my_list))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 什么是列表中的列表？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 列表中的元素依然是列表\n",
    "my_list_list = [ [1, 2 ,3 ,4], [5, 6, 7] ]\n",
    "print(\"1）\",my_list_list)\n",
    "print(\"2）\",my_list_list[0])\n",
    "print(\"3）\",my_list_list[0][0])\n",
    "# 实际上还可以列表中的列表中的列表...，可以尝试一下"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 读取Excel，获取员工姓名和明细数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建两个“空”列表变量\n",
    "staff_names = [] # 员工姓名列表\n",
    "staff_values_list = [] # 员工数据明细列表（这个就是列表中的列表）\n",
    "\n",
    "# 因为第一行（索引=0）是字段名，所以从第二行（索引=1）开始读数据\n",
    "for row_index in range(1, sheet_rd.nrows):\n",
    "    values = sheet_rd.row_values(row_index)  # 获取员工的数据列表\n",
    "    # print(values)           # 可以删除注释，打印这一行数据列表看看\n",
    "    staff_name = values[2]    # 第三列是姓名\n",
    "    # print(staff_name)       # 可以删除注释，打印一下看看\n",
    "    staff_names.append(staff_name)  # append\n",
    "    staff_values_list.append(values) # 列表中可以再包含列表 [[...],[...]]\n",
    "\n",
    "# 打印读取的员工姓名列表、员工数据明细列表\n",
    "# print(\"员工姓名列表：\",staff_names)           # 可以删除注释，打印一下看看\n",
    "# print(\"员工数据明细列表：\",staff_values_list) # 可以删除注释，打印一下看看"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 新建 Excel，创建样式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建写入工作簿对象\n",
    "workbook_wt = xlwt.Workbook(encoding='utf-8')\n",
    "\n",
    "# 字段样式（背景=草绿，文字颜色=白色，字号=12，水平对齐=靠右）\n",
    "field_style = create_style(back_color=57, font_color=1, font_size=12, horz=3)\n",
    "# 数据值的样式（背景=白色，文字颜色=黑色，字号=12，水平对齐=居中）\n",
    "value_style = create_style(back_color=1, font_color=0, font_size=12, horz=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue>Excel颜色索引对照表</font>**<br/>\n",
    "<img src=\"images/Excel颜色对照表.png\" align=\"left\"></img>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 复习一下双循环的用法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 单循环（range） \n",
    "for x in range(1,4):\n",
    "    print(\"x=%s\" % x) # print 的新用法 %s 的值就是后面的 x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 单循环（列表） \n",
    "names = ['张三', '李四', '王五']\n",
    "for name in names:\n",
    "    print(\"姓名：%s\" % name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 单循环（列表 + enumerate ） \n",
    "names = ['张三', '李四', '王五']\n",
    "# 加入列举（枚举）函数 enumerate(names) 后\n",
    "# 多了循环变量 index（索引值从 0 开始）\n",
    "for index, name in enumerate(names): \n",
    "    print(\"编号：%s；姓名：%s\" % (index, name))  # 按照顺序，两个 %s 的值分别是 index, name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 双循环（range）\n",
    "for x in range(1,4):     # 1, 2, 3\n",
    "    for y in range(5,7): # 5, 6\n",
    "        print(\"x=%s；y=%s\" % (x, y))  # 按照顺序，两个 %s 的值分别是 x, y "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 双循环（列表），深入理解后，才能进入下一步 \n",
    "names = ['张三', '李四', '王红']\n",
    "values = [1, 2, 3]\n",
    "for index, name in enumerate(names): \n",
    "    print(\"%s(编号：%s); \" % (name,index))\n",
    "    for value in values:\n",
    "        print(value)   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 双循环（列表 + enumerate ），深入理解后，才能进入下一步 \n",
    "names = ['张三', '李四', '王红']\n",
    "values_list = [ [35,'男'], [28,'男'], [20,'女'] ]\n",
    "for index, name in enumerate(names): \n",
    "    print(\"%s(编号：%s); \" % (name,index))\n",
    "    values = values_list[index]\n",
    "    for value in values:\n",
    "        print(value)   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 双循环（列表 + enumerate ），深入理解后，才能进入下一步 \n",
    "names = ['张三', '李四', '王红']\n",
    "fields = ['年龄','性别']\n",
    "values_list = [ [35,'男'], [28,'男'], [20,'女'] ]\n",
    "for name_idx, name in enumerate(names): \n",
    "    print(\"%s(编号：%s); \" % (name,name_idx))\n",
    "    values = values_list[name_idx]\n",
    "    for val_idx, value in enumerate(values):\n",
    "        field = fields[val_idx]\n",
    "        print(\" - %s：%s\" % (field,value))   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 两个循环写入数据 ，保存 Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 第一循环：遍历每个员工的姓名，每个姓名创建一个工作表\n",
    "for staff_index, staff_name in enumerate(staff_names):\n",
    "    # 每个员工创建一个工作表，工作表名就是员工姓名\n",
    "    sheet_wt_staff = workbook_wt.add_sheet(staff_name)\n",
    "\n",
    "    # 获取员工的数据明细\n",
    "    staff_data = staff_values_list[staff_index]\n",
    "    # 第二循环：遍历字段和数据\n",
    "    for field_index, field_name in enumerate(field_names):\n",
    "        row = field_index + 1 # field_index 从 0 开始，则 row 从 1 开始\n",
    "        # 第3列（c=2）写入字段名\n",
    "        sheet_wt_staff.write(r=row, c=2, label=field_name, style=field_style)\n",
    "\n",
    "        value = staff_data[field_index]\n",
    "        # 第4列写入数据（索引=3）\n",
    "        sheet_wt_staff.write(r=row, c=3, label=value, style=value_style)\n",
    "\n",
    "    # 设置字段列和数据列的列宽 256 × 宽度默认单位（字符）\n",
    "    sheet_wt_staff.col(2).width = 256 * 15\n",
    "    sheet_wt_staff.col(3).width = 256 * 20\n",
    "\n",
    "workbook_wt.save(xls_filename_wt) # 保存 Excel\n",
    "\n",
    "print(\"文件 %s 生成成功\" % xls_filename_wt) # 打印成功的信息（注意： %s = xls_filename_wt）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python总结"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue size=4> 1、列表的增加、删除方法以及列表中的列表</font>**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**<font color=blue size=4> 2、需要深入理解两个循环内的代码逻辑</font>**\n",
    "\n",
    "<font color=blue size=4> 两个循环内的代码逻辑对于初学者来讲，会有一些困难，但没有关系，大家可以反复多看看视频，加入print代码，尝试运行看看，多思考</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 完整代码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlwt\n",
    "\n",
    "# 从（from）本地 xlwt_styles.py（.py扩展名省略）文件中，导入（import）创建样式的函数（create_style）\n",
    "from xlwt_styles import create_style\n",
    "\n",
    "# 定义两个变量（xls_filename_rd、xls_filename_wt）\n",
    "xls_filename_rd = \"./files/Excel_工资单明细.xls\" # 读取的 Excel 文件名\n",
    "xls_filename_wt = \"./files/Excel_员工工资单_01.xls\" # 新建的 Excel 文件名\n",
    "# *******************************\n",
    "# 虽然变量命名只要符合命名规则即可，但建议变量命名最好要有意义，对读懂代码很有帮助\n",
    "# xls:Excel扩展名  filename:文件名  rd:read读取  wt:write写入\n",
    "# *******************************\n",
    "\n",
    "# 读取 Excel（xls_filename_rd 变量刚才已经定义），得到工作簿，赋值给变量 workbook_rd\n",
    "workbook_rd = xlrd.open_workbook(xls_filename_rd)\n",
    "\n",
    "# 获取第一个工作表，赋值给变量 sheet_rd\n",
    "sheet_rd = workbook_rd.sheet_by_index(0)\n",
    "\n",
    "# 获取工作表中表格的所有字段（第一行是字段名），记住后面会用到\n",
    "field_names = sheet_rd.row_values(0)\n",
    "print(\"字段名：\",field_names)  # 打印一下看看字段名\n",
    "\n",
    "# 创建写入工作簿对象\n",
    "workbook_wt = xlwt.Workbook(encoding='utf-8')\n",
    "\n",
    "# 字段样式（背景=草绿，文字颜色=白色，字号=12，水平对齐=靠右）\n",
    "field_style = create_style(back_color=57, font_color=1, font_size=12, horz=3)\n",
    "# 数据值的样式（背景=白色，文字颜色=黑色，字号=12，水平对齐=居中）\n",
    "value_style = create_style(back_color=1, font_color=0, font_size=12, horz=2)\n",
    "\n",
    "# 第一循环：遍历每个员工的姓名，每个姓名创建一个工作表\n",
    "for staff_index, staff_name in enumerate(staff_names):\n",
    "    # 每个员工创建一个工作表，工作表名就是员工姓名\n",
    "    sheet_wt_staff = workbook_wt.add_sheet(staff_name)\n",
    "\n",
    "    # 获取员工的数据明细\n",
    "    staff_data = staff_values_list[staff_index]\n",
    "    # 第二循环：遍历字段和数据\n",
    "    for field_index, field_name in enumerate(field_names):\n",
    "        row = field_index + 1 # field_index 从 0 开始，则 row 从 1 开始\n",
    "        # 第3列（c=2）写入字段名\n",
    "        sheet_wt_staff.write(r=row, c=2, label=field_name, style=field_style)\n",
    "\n",
    "        value = staff_data[field_index]\n",
    "        # 第4列写入数据（索引=3）\n",
    "        sheet_wt_staff.write(r=row, c=3, label=value, style=value_style)\n",
    "\n",
    "    # 设置字段列和数据列的列宽 256 × 宽度默认单位（字符）\n",
    "    sheet_wt_staff.col(2).width = 256 * 15\n",
    "    sheet_wt_staff.col(3).width = 256 * 20\n",
    "\n",
    "workbook_wt.save(xls_filename_wt) # 保存 Excel\n",
    "\n",
    "print(\"文件 %s 生成成功\" % xls_filename_wt) # 打印成功的信息（注意： %s = xls_filename_wt）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 思考题"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color=blue size=4>工作表给单元格写入数据的代码如下：</font>\n",
    "\n",
    "<font color=red size=4>sheet.write(r=row, c=column, label=value, style=value_style)  </font>\n",
    "\n",
    "<font color=blue size=4>按照“面向对象编程”的设计思路，如果我们要设计一个合并单元格的方法 </font>\n",
    "\n",
    "<font color=blue size=4>1、合并单元格方法的名称是什么？ </font>\n",
    "\n",
    "<font color=blue size=4>2、合并单元格方法需要传入哪些参数？ </font>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
